﻿Imports System.Data.SqlClient

Public Class cContract

    Dim con As New SqlConnection(strcon)

    'For choose for caseincome
    Public Function select_Contract_All_CaseIncome() As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.*,l.LawyerName,p.CompanyName FROM tblContract c INNER JOIN tblLawyer l ON c.LawyerID=l.LawyerID INNER JOIN tblCompany p ON c.ContractID=p.ContractID")
        Return dt
    End Function

    Public Function select_Contract_Finish_CaseIncome() As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.*,l.LawyerName,p.CompanyName FROM tblContract c INNER JOIN tblLawyer l ON c.LawyerID=l.LawyerID INNER JOIN tblCompany p ON c.ContractID=p.ContractID WHERE IsFinish=1")
        Return dt
    End Function

    Public Function select_Contract_NotFinish_CaseIncome() As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.*,l.LawyerName,p.CompanyName FROM tblContract c INNER JOIN tblLawyer l ON c.LawyerID=l.LawyerID INNER JOIN tblCompany p ON c.ContractID=p.ContractID WHERE IsFinish=0")
        Return dt
    End Function

    Public Function select_Contract_All_CaseIncome_byKeyword(ByVal Keyword As String) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.*,l.LawyerName,p.CompanyName FROM tblContract c INNER JOIN tblLawyer l ON c.LawyerID=l.LawyerID INNER JOIN tblCompany p ON c.ContractID=p.ContractID WHERE LOWER(p.CompanyName + l.LawyerName + CONVERT(nvarchar,c.ContractFee) + CONVERT(nvarchar,c.Duration) + CONVERT(nvarchar,c.StartDate) + CONVERT(nvarchar,c.EndDate)) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function

    Public Function select_Contract_Finish_CaseIncome_byKeyword(ByVal Keyword As String) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.*,l.LawyerName,p.CompanyName FROM tblContract c INNER JOIN tblLawyer l ON c.LawyerID=l.LawyerID INNER JOIN tblCompany p ON c.ContractID=p.ContractID WHERE IsFinish=1 AND LOWER(p.CompanyName + l.LawyerName + CONVERT(nvarchar,c.ContractFee) + CONVERT(nvarchar,c.Duration) + CONVERT(nvarchar,c.StartDate) + CONVERT(nvarchar,c.EndDate)) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function

    Public Function select_Contract_NotFinish_CaseIncome_byKeyword(ByVal Keyword As String) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.*,l.LawyerName,p.CompanyName FROM tblContract c INNER JOIN tblLawyer l ON c.LawyerID=l.LawyerID INNER JOIN tblCompany p ON c.ContractID=p.ContractID WHERE IsFinish=0 AND LOWER(p.CompanyName + l.LawyerName + CONVERT(nvarchar,c.ContractFee) + CONVERT(nvarchar,c.Duration) + CONVERT(nvarchar,c.StartDate) + CONVERT(nvarchar,c.EndDate)) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function
    'End For choose for caseincome

    'For View
    Public Function select_Contract_All(ByVal LawyerID As Long) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.*,l.LawyerName,p.CompanyName FROM tblContract c INNER JOIN tblLawyer l ON c.LawyerID=l.LawyerID INNER JOIN tblCompany p ON c.ContractID=p.ContractID WHERE c.LawyerID=N'" & LawyerID & "'")
        Return dt
    End Function

    Public Function select_Contract_NotFinish(ByVal LawyerID As Long) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.*,l.LawyerName,p.CompanyName FROM tblContract c INNER JOIN tblLawyer l ON c.LawyerID=l.LawyerID INNER JOIN tblCompany p ON c.ContractID=p.ContractID WHERE c.LawyerID=N'" & LawyerID & "' AND IsFinish=0")
        Return dt
    End Function

    Public Function select_Contract_Finish(ByVal LawyerID As Long) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.*,l.LawyerName,p.CompanyName FROM tblContract c INNER JOIN tblLawyer l ON c.LawyerID=l.LawyerID INNER JOIN tblCompany p ON c.ContractID=p.ContractID WHERE c.LawyerID=N'" & LawyerID & "' AND IsFinish=1")
        Return dt
    End Function
    'End For View

    'For Search
    Public Function select_Contract_All_byKeyword(ByVal LawyerID As Long, ByVal Keyword As String) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.*,l.LawyerName,p.CompanyName FROM tblContract c INNER JOIN tblLawyer l ON c.LawyerID=l.LawyerID INNER JOIN tblCompany p ON c.ContractID=p.ContractID WHERE c.LawyerID=N'" & LawyerID & "' AND LOWER(p.CompanyName + l.LawyerName + CONVERT(nvarchar,c.ContractFee) + CONVERT(nvarchar,c.Duration) + CONVERT(nvarchar,c.StartDate) + CONVERT(nvarchar,c.EndDate)) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function

    Public Function select_Contract_NotFinish_byKeyword(ByVal LawyerID As Long, ByVal Keyword As String) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.*,l.LawyerName,p.CompanyName FROM tblContract c INNER JOIN tblLawyer l ON c.LawyerID=l.LawyerID INNER JOIN tblCompany p ON c.ContractID=p.ContractID WHERE c.LawyerID=N'" & LawyerID & "' AND IsFinish=0 AND LOWER(p.CompanyName + l.LawyerName + CONVERT(nvarchar,c.ContractFee) + CONVERT(nvarchar,c.Duration) + CONVERT(nvarchar,c.StartDate) + CONVERT(nvarchar,c.EndDate)) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function

    Public Function select_Contract_Finish_byKeyword(ByVal LawyerID As Long, ByVal Keyword As String) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.*,l.LawyerName,p.CompanyName FROM tblContract c INNER JOIN tblLawyer l ON c.LawyerID=l.LawyerID INNER JOIN tblCompany p ON c.ContractID=p.ContractID WHERE c.LawyerID=N'" & LawyerID & "' AND IsFinish=1 AND LOWER(p.CompanyName + l.LawyerName + CONVERT(nvarchar,c.ContractFee) + CONVERT(nvarchar,c.Duration) + CONVERT(nvarchar,c.StartDate) + CONVERT(nvarchar,c.EndDate)) LIKE N'%' + N'" & LCase(Keyword) & "' + N'%'")
        Return dt
    End Function
    'End For Search

    Public Function select_Contract_byID(ByVal ContractID As Long) As DataTable
        Dim dt As New DataTable
        Dim oMain As New cMain
        dt = oMain.getData("SELECT c.ContractID,co.CompanyID,co.CompanyName,c.LawyerID,c.ContractFee,c.Duration,c.StartDate,c.EndDate,c.IsFinish,c.UserID FROM tblContract c INNER JOIN tblCompany co ON c.ContractID=co.ContractID WHERE c.ContractID=N'" & ContractID & "'")
        Return dt
    End Function

    Public Function insert_Contract(ByVal ContractID As Integer, ByVal LawyerID As Long, ByVal ContractFee As Double, ByVal Duration As Integer, ByVal StartDate As Date, ByVal EndDate As Date, ByVal UserID As String)
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "UPDATE tblContract SET LawyerID=@LawyerID,ContractFee=@ContractFee,Duration=@Duration,StartDate=@StartDate,EndDate=@EndDate,UserID=@UserID WHERE ContractID=@ContractID"
        With cmd.Parameters
            .AddWithValue("@ContractID", ContractID)
            .AddWithValue("@LawyerID", LawyerID)
            .AddWithValue("@ContractFee", ContractFee)
            .AddWithValue("@Duration", Duration)
            .AddWithValue("@StartDate", StartDate)
            .AddWithValue("@EndDate", EndDate)
            .AddWithValue("@UserID", UserID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function insert_Contract_ID(ByVal ContractID As Long) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "INSERT INTO tblContract(ContractID) VALUES(@ContractID)"
        With cmd.Parameters
            .AddWithValue("@ContractID", ContractID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function delete_Contract_ID(ByVal ContractID As Long) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "DELETE FROM tblContract WHERE ContractID=@ContractID"
        With cmd.Parameters
            .AddWithValue("@ContractID", ContractID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function update_Contract_Finish(ByVal ContractID As Long) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "UPDATE tblContract SET IsFinish=1 WHERE ContractID=@ContractID"
        With cmd.Parameters
            .AddWithValue("@ContractID", ContractID)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

    Public Function update_Contract_Finish_ForClickButton(ByVal ContractID As Long, ByVal Duration As Integer, ByVal EndDate As Date) As Boolean
        Dim rv As Boolean = True
        Dim cmd As New SqlCommand
        cmd.CommandText = "UPDATE tblContract SET Duration=@Duration,EndDate=@EndDate,IsFinish=1 WHERE ContractID=@ContractID"
        With cmd.Parameters
            .AddWithValue("@ContractID", ContractID)
            .AddWithValue("@Duration", Duration)
            .AddWithValue("@EndDate", EndDate)
        End With
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            rv = False
        Finally
            con.Close()
            cmd.Dispose()
        End Try
        Return rv
    End Function

End Class
